﻿Imports System.Data.SqlClient
Imports System.Data
Public Class BookDetails_mobile
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        SqlDataSource6.SelectParameters("Bibliophile").DefaultValue = User.Identity.Name

        myHeading.InnerHtml = "Questions"

        If IsPostBack = False Then
            'Populate the Comments
            Comments.GetComments(Request.Params("ID"), CommentsLtr)

            'Populate the tag cloud
            TagCloud.getTagCloud(SqlDataSource4, CloudTags, Page)

            'Opening the connection
            Dim conn As New SqlConnection(ConfigurationManager.ConnectionStrings("BooksReviewDBConnectionString1").ConnectionString.ToString)
            conn.Open()

            'Count Page Activity
            Dim cmdPageActivity As New SqlCommand("INSERT INTO Site_Activity(Activity) VALUES (@f1)", conn)
            cmdPageActivity.CommandType = CommandType.Text
            cmdPageActivity.Parameters.AddWithValue("@f1", "Book Details")
            cmdPageActivity.ExecuteNonQuery()
            conn.Close()

            'Checking if user has selected this book as favorite book
            Dim cmdFavoriteBook As New SqlCommand("SELECT * FROM Favorite_Book WHERE Bibliophile = @f1 AND Book_FK = @f2", conn)
            cmdFavoriteBook.CommandType = CommandType.Text
            cmdFavoriteBook.Parameters.AddWithValue("@f1", User.Identity.Name)
            cmdFavoriteBook.Parameters.AddWithValue("@f2", Request.Params("ID"))
            'Getting records (only 1 line for selected bibliophile)
            conn.Open()
            Dim dr As SqlDataReader = cmdFavoriteBook.ExecuteReader()
            If dr.HasRows Then
                dr.Read()
                If Not IsDBNull(dr("Book_FK")) Then
                    btn_Favorites.Text = "Your Favorite Book"
                    btn_Favorites.Enabled = False
                End If
            End If
            conn.Close()

            'Check if user has add this book to his guide
            Dim cmdGuide As New SqlCommand("SELECT * FROM Guides WHERE GuideCreator = @f1 AND Book_FK = @f2", conn)
            cmdGuide.CommandType = CommandType.Text
            cmdGuide.Parameters.AddWithValue("@f1", User.Identity.Name)
            cmdGuide.Parameters.AddWithValue("@f2", Request.Params("ID"))
            'Getting records (only 1 line for selected tourist)
            conn.Open()
            Dim drGuide As SqlDataReader = cmdGuide.ExecuteReader()
            If drGuide.HasRows Then
                drGuide.Read()
                If Not IsDBNull(drGuide("Book_FK")) Then
                    btn_AddGuide.Text = "Added to Guide"
                    btn_AddGuide.Enabled = False
                End If
            End If
            conn.Close()

            'Checking if user has rated this book
            conn.Open()
            Dim checkRatingBook As New SqlCommand("SELECT * FROM BookUserRating WHERE Bibliophile = @f1 AND Book_FK = @f2", conn)

            checkRatingBook.CommandType = CommandType.Text
            checkRatingBook.Parameters.AddWithValue("@f1", User.Identity.Name)
            checkRatingBook.Parameters.AddWithValue("@f2", Request.Params("ID"))
            'Getting records (only 1 line for selected tourist)
            Dim drRating As SqlDataReader = checkRatingBook.ExecuteReader()
            If drRating.HasRows Then
                drRating.Read()
                If Not IsDBNull(drRating("Book_FK")) Then
                    btn_Rating.Visible = False
                    btn_Change.Visible = True
                    Repeater8.Visible = True
                    Rating1.Visible = False
                End If
            End If
            conn.Close()

            'Counting the activity that the user has viewed this particular beach
            Dim cmdViewed As New SqlCommand("INSERT INTO Books_Viewed (Bibliophile, Book_FK) VALUES (@f1, @f2)", conn)
            cmdViewed.CommandType = CommandType.Text
            cmdViewed.Parameters.AddWithValue("@f1", User.Identity.Name)
            cmdViewed.Parameters.AddWithValue("@f2", Request.Params("ID"))
            conn.Open()
            cmdViewed.ExecuteNonQuery()

            'Check if user likes this beach
            Dim cmdLikes As New SqlCommand("SELECT * FROM Book_Likes WHERE Book_FK = @f1 AND Bibliophile = @f2", conn)
            cmdLikes.Parameters.AddWithValue("@f1", Request.Params("ID"))
            cmdLikes.Parameters.AddWithValue("@f2", User.Identity.Name)
            'Find out if there any records at all (returned)
            Dim drLikes As SqlDataReader = cmdLikes.ExecuteReader()
            'this shows if any data is returned
            If drLikes.HasRows Then
                btn_Likes.Enabled = False
                btn_Likes.Text = "Liked"
            Else
                btn_Likes.Enabled = True
                btn_Likes.Text = "Like it"
            End If

            'Closing and disposing the connection
            conn.Close()
            conn.Dispose()
        End If
    End Sub

    Protected Function getViews(ByVal bookID As Integer) As String
        'Return the total views for this page.
        Dim result As String = ""
        Dim conn As New SqlConnection(ConfigurationManager.ConnectionStrings("BooksReviewDBConnectionString1").ConnectionString.ToString)
        Dim cmd As New SqlCommand("SELECT * FROM TotalViewsForAllBooks WHERE ID = @f1", conn)

        cmd.CommandType = CommandType.Text
        cmd.Parameters.AddWithValue("@f1", bookID)
        conn.Open()
        Dim dr As SqlDataReader = cmd.ExecuteReader()
        dr.Read()
        result = dr("TotalViews")
        conn.Close()
        Return result
    End Function

    Protected Sub Button_Click(sender As Object, e As EventArgs)
        'My intention was to create one function which will add comments and replies.
        'However i couldn't create dynamically asp net buttons inside the literal which displays comments and replies
        'So this function is only used for adding comments.
        Dim btn As Button = DirectCast(sender, Button)
        Dim conn As New SqlConnection(ConfigurationManager.ConnectionStrings("BooksReviewDBConnectionString1").ConnectionString.ToString)
        Dim cmd As New SqlCommand("INSERT INTO [Comments] ([CommentsName], [Title], [Book_FK], [CommentsBody], [ReplyTo]) VALUES (@f1, @f2, @f3, @f4, @f5)", conn)

        conn.Open()
        cmd.CommandType = CommandType.Text
        cmd.Parameters.AddWithValue("@f1", User.Identity.Name)
        cmd.Parameters.AddWithValue("@f2", tb_Title.Text)
        cmd.Parameters.AddWithValue("@f3", Request.Params("ID"))
        cmd.Parameters.AddWithValue("@f4", tb_Comment.Text)
        cmd.Parameters.AddWithValue("@f5", btn.CommandArgument)
        cmd.ExecuteNonQuery()
        conn.Close()
        conn.Dispose()
        Comments.GetComments(Request.Params("ID"), CommentsLtr)
        getTotalComments()
        tb_Comment.Text = ""
        tb_Title.Text = ""
    End Sub

    Protected Function getTotalComments() As String
        Dim conn As New SqlConnection(ConfigurationManager.ConnectionStrings("BooksReviewDBConnectionString1").ConnectionString.ToString)
        Dim cmd As New SqlCommand("SELECT * FROM TotalCommentsPerBook WHERE ID = @f1", conn)
        conn.Open()
        cmd.CommandType = CommandType.Text
        cmd.Parameters.AddWithValue("@f1", Request.Params("ID"))
        Dim dr As SqlDataReader = cmd.ExecuteReader()
        dr.Read()
        Dim retValue As Integer = dr("TotalComments")
        If retValue <> 1 Then
            Return dr("TotalComments") & " Comments"
        Else
            Return dr("TotalComments") & " Comment"
        End If
        conn.Close()
        conn.Dispose()
    End Function

    Protected Sub btn_Favorites_Click(sender As Object, e As EventArgs) Handles btn_Favorites.Click
        Dim conn As New SqlConnection(ConfigurationManager.ConnectionStrings("BooksReviewDBConnectionString1").ConnectionString.ToString)
        Dim cmd As New SqlCommand("UPDATE Favorite_Book SET Book_FK = @f1 WHERE Bibliophile = @f2", conn)
        cmd.CommandType = CommandType.Text
        cmd.Parameters.AddWithValue("@f1", Request.Params("ID"))
        cmd.Parameters.AddWithValue("@f2", User.Identity.Name)
        conn.Open()
        cmd.ExecuteNonQuery()
        'Eliminate cmd and conn from memory
        cmd.Dispose()
        conn.Close()
        conn.Dispose()

        btn_Favorites.Text = "Your Favorite Book"
        btn_Favorites.Enabled = False
    End Sub

    Protected Sub btn_AddGuide_Click(sender As Object, e As EventArgs) Handles btn_AddGuide.Click
        Dim conn As New SqlConnection(ConfigurationManager.ConnectionStrings("BooksReviewDBConnectionString1").ConnectionString.ToString)
        Dim cmd As New SqlCommand("INSERT INTO GUIDES (Book_FK, GuideCreator) VALUES (@f1, @f2)", conn)
        cmd.CommandType = CommandType.Text
        cmd.Parameters.AddWithValue("@f1", Request.Params("ID"))
        cmd.Parameters.AddWithValue("@f2", User.Identity.Name)
        conn.Open()
        cmd.ExecuteNonQuery()
        'Eliminate cmd and oledbconn from memory
        cmd.Dispose()
        conn.Close()
        conn.Dispose()

        btn_AddGuide.Text = "Added To Guide"
        btn_AddGuide.Enabled = False
    End Sub

    Protected Sub btn_Likes_Click(sender As Object, e As EventArgs) Handles btn_Likes.Click
        Dim conn As New SqlConnection(ConfigurationManager.ConnectionStrings("BooksReviewDBConnectionString1").ConnectionString.ToString)
        Dim cmd As New SqlCommand("INSERT INTO Book_Likes (Book_FK, Bibliophile) VALUES (@f1, @f2)", conn)
        conn.Open()

        cmd.Parameters.AddWithValue("@f1", Request.Params("ID"))
        cmd.Parameters.AddWithValue("@f2", User.Identity.Name)
        cmd.ExecuteNonQuery()
        conn.Close()
        conn.Dispose()
        cmd.Dispose()
        'hide button
        rpt_Likes.DataBind()
        btn_Likes.Enabled = False
        btn_Likes.Text = "Liked"
    End Sub

    Protected Sub btn_Tag_Click(sender As Object, e As EventArgs) Handles btn_Tag.Click
        Dim conn As New SqlConnection(ConfigurationManager.ConnectionStrings("BooksReviewDBConnectionString1").ConnectionString.ToString)
        Dim cmd As New SqlCommand("INSERT INTO BookTagJunction (Book_FK, Tag_FK, Bibliophile) VALUES (@f1, @f2, @f3)", conn)
        conn.Open()
        For Each cd As ListItem In cbl_Tags.Items
            If cd.Selected Then
                'Check if user has added the same tag already
                Dim cmdCheck As New SqlCommand("SELECT COUNT(*) FROM BookTagJunction WHERE Book_FK = @f4 AND Bibliophile = @f5 AND Tag_FK = @f6", conn)

                cmdCheck.CommandType = CommandType.Text
                cmdCheck.Parameters.AddWithValue("@f4", Request.Params("ID"))
                cmdCheck.Parameters.AddWithValue("@f5", User.Identity.Name)
                cmdCheck.Parameters.AddWithValue("@f6", cd.Value)
                Dim userCount As Integer = cmdCheck.ExecuteScalar()
                If (userCount > 0) Then
                    lbl_Tag.Text = "You have already added that tag"
                Else
                    'Add the join to the table
                    cmd.CommandType = CommandType.Text
                    cmd.Parameters.AddWithValue("@f1", Request.Params("ID"))
                    cmd.Parameters.AddWithValue("@f2", cd.Value)
                    cmd.Parameters.AddWithValue("@f3", User.Identity.Name)
                    cmd.ExecuteNonQuery()
                    lbl_Tag.Text = ""
                End If
            End If
        Next
        'check if text box is empty
        If tb_Tags.Text = "" Then
            conn.Close()
            TagCloud.getTagCloud(SqlDataSource4, CloudTags, Page)
            cbl_Tags.ClearSelection()
            tb_Tags.Text = ""
        Else
            Dim newTags = tb_Tags.Text.Split(",")
            For Each newTag In newTags
                'Check if tag already exists

                Dim cdTemp As Integer
                cdTemp = -1
                For Each cd As ListItem In cbl_Tags.Items
                    If cd.Text.ToLower.Equals(newTag.ToLower) Then
                        cdTemp = cd.Value
                    End If
                Next
                If cdTemp = -1 Then
                    'Add new tag to the Tags table
                    Dim newTagSql As New SqlCommand("INSERT INTO Tags(Tag) VALUES (@f1)", conn)
                    newTagSql.CommandType = CommandType.Text
                    newTagSql.Parameters.AddWithValue("@f1", newTag)
                    newTagSql.ExecuteNonQuery()

                    'Find id of the new tag.
                    Dim getLastIdCmd As New SqlCommand("SELECT TOP 1 ID FROM Tags ORDER BY ID DESC", conn)
                    Dim dr = getLastIdCmd.ExecuteReader()
                    dr.Read()
                    Dim lastId = dr.GetValue(0)

                    'Assossiate this last id with the tag id
                    Dim newCmd As New SqlCommand("INSERT INTO BookTagJunction (Book_FK, Tag_FK, Bibliophile) VALUES (@f1, @f2, @f3)", conn)
                    newCmd.CommandType = CommandType.Text
                    newCmd.Parameters.AddWithValue("@f1", Request.Params("ID"))
                    newCmd.Parameters.AddWithValue("@f2", lastId)
                    newCmd.Parameters.AddWithValue("@f3", User.Identity.Name)
                    newCmd.ExecuteNonQuery()

                Else

                    'Check if user has added the same tag already
                    Dim cmdNewCheck As New SqlCommand("SELECT COUNT(*) FROM BookTagJunction WHERE Book_FK = @f1 AND Bibliophile = @f2 AND Tag_FK = @f3", conn)

                    cmdNewCheck.CommandType = CommandType.Text
                    cmdNewCheck.Parameters.AddWithValue("@f1", Request.Params("ID"))
                    cmdNewCheck.Parameters.AddWithValue("@f2", User.Identity.Name)
                    cmdNewCheck.Parameters.AddWithValue("@f3", cdTemp)
                    Dim userNewCount As Integer = cmdNewCheck.ExecuteScalar()
                    If (userNewCount = 0) Then

                        'Assossiate this last id with the hotel id
                        Dim newCmd1 As New SqlCommand("INSERT INTO BookTagJunction (Book_FK, Tag_FK, Bibliophile) VALUES (@f1, @f2, @f3)", conn)
                        newCmd1.CommandType = CommandType.Text
                        newCmd1.Parameters.AddWithValue("@f1", Request.Params("ID"))
                        newCmd1.Parameters.AddWithValue("@f2", cdTemp)
                        newCmd1.Parameters.AddWithValue("@f3", User.Identity.Name)
                        newCmd1.ExecuteNonQuery()
                    End If
                End If
                cdTemp = -1
            Next
            conn.Close()
            cbl_Tags.DataBind()
            TagCloud.getTagCloud(SqlDataSource4, CloudTags, Page)
            cbl_Tags.ClearSelection()
            tb_Tags.Text = ""
        End If
    End Sub

    Protected Sub btn_Rating_Click(sender As Object, e As EventArgs) Handles btn_Rating.Click
        'If user has selected a rating
        If Rating1.CurrentRating > 0 Then
            Dim conn As New SqlConnection(ConfigurationManager.ConnectionStrings("BooksReviewDBConnectionString1").ConnectionString.ToString)
            Dim delRatingCmd As New SqlCommand("DELETE FROM BookUserRating WHERE Book_FK=@f1 AND Bibliophile=@f2", conn)
            conn.Open()
            'Delete user's rating
            delRatingCmd.Parameters.AddWithValue("@f1", Request.Params("ID"))
            delRatingCmd.Parameters.AddWithValue("@f2", User.Identity.Name)
            delRatingCmd.ExecuteNonQuery()
            'Add new rating
            Dim addRatingCmd As New SqlCommand("INSERT INTO BookUserRating (Book_FK, UserRating, Bibliophile) VALUES (@f1, @f2, @f3)", conn)

            addRatingCmd.Parameters.AddWithValue("@f1", Request.Params("ID"))
            addRatingCmd.Parameters.AddWithValue("@f2", Rating1.CurrentRating)
            addRatingCmd.Parameters.AddWithValue("@f3", User.Identity.Name)
            addRatingCmd.ExecuteNonQuery()
            conn.Close()
            conn.Dispose()
            addRatingCmd.Dispose()
            'Update repeaters which display the average rating and current rating
            Repeater7.DataBind()
            Repeater8.DataBind()

            btn_Rating.Visible = False
            btn_Change.Visible = True
            Repeater8.Visible = True
            Rating1.Visible = False
        End If
    End Sub

    Protected Sub btn_Change_Click(sender As Object, e As EventArgs) Handles btn_Change.Click
        btn_Rating.Visible = True
        btn_Change.Visible = False
        Repeater8.Visible = False
        Rating1.Visible = True
    End Sub

    Protected Sub SqlDataSource7_Selected(sender As Object, e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles SqlDataSource7.Selected
        'If datasource is empty
        If (e.AffectedRows < 1) Then
            myHeading.InnerHtml = "No Questions found"
        End If
    End Sub



    Protected Function getRating(ByVal rating As Integer) As String
        Select Case (rating)
            Case Is = 1
                Return "Images/Rating/1-star-rating.png"
            Case Is = 2
                Return "Images/Rating/2-star-rating.png"
            Case Is = 3
                Return "Images/Rating/3-star-rating.png"
            Case Is = 4
                Return "Images/Rating/4-star-rating.png"
            Case Is = 5
                Return "Images/Rating/5-star-rating.png"
        End Select
        Return Nothing
    End Function
End Class